package com.iplant.mes.serviceImpl.dao.sfc;

import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.annotation.dao.DBBaseDAO;
import com.iplant.base.utils.cache.CacheUtils;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.basicenum.stat.StatTypes;
import com.iplant.basic.service.po.bms.BMSEmployee;
import com.iplant.mes.service.mesenum.sfc.SFCTaskStatus;
import com.iplant.mes.service.po.andon.productData.AndonOrderRate;
import com.iplant.mes.service.po.andon.productData.AndonProductRate;
import com.iplant.mes.service.po.qms.QMSPassRate;
import com.iplant.mes.service.po.sfc.SFCWorkpieceRecord;
import com.iplant.pdm.service.basicenum.pdm.PDMComponentTypes;
import com.iplant.pdm.service.po.fpc.FPCProduct;
import lombok.extern.slf4j.Slf4j;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

@DBBaseDAO(SFCWorkpieceRecord.class)
@Slf4j
public class SFCWorkpieceRecordDAO extends BaseDAO {

    private static SFCWorkpieceRecordDAO Instance = null;

    public static SFCWorkpieceRecordDAO getInstance() {
        if (Instance == null) {
            Instance = new SFCWorkpieceRecordDAO();
        }
        return Instance;
    }

    public SFCWorkpieceRecordDAO() {
        super("");
        this.Init();
    }


    /**
     * 订单趋势统计
     *
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStatType
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassRate> QMS_GetPassRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID,
                                                 String wOrderNo, int wStatType,
                                                 Calendar wStartDate, Calendar wEndDate,
                                                 OutResult<Integer> wErrorCode) {
        List<QMSPassRate> wResult = new ArrayList<>();
        try {
            StatTypes wType = StatTypes.getEnumType(wStatType);
            String wGroupType = "";
            switch (wType) {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case Month:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(t.OffLineTime),'-',(quarter(t.OffLineTime) * 3)-2,'-1')" +
                            ",'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(t.OffLineTime),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }

            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText = StringUtils.Format("SELECT t.OrderID,t.OrderNo,t.ProductID,  {1}  as StatTypeDate," +
                    "  count(case when t.Status=:wFinishStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as GoodFQTY," +
                    "  count(case when t.ReworkFlag=1 and t.ComponentType= :wComType then t.ID else " +
                    "null end) as ReworkComFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as BadFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wComType then t.ID else null end)" +
                    " as BadComFQTY" +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t" +
                    ".OffLineTime) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.OffLineTime)  " +
                    "  GROUP BY t.OrderID, StatTypeDate", getTableName(), wGroupType);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQLText, wParams);

            if (wQueryResult == null || wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FPCProduct> wFPCProductMap = CacheUtils.GetCacheTList(FPCProduct.class).stream()
                    .collect(Collectors.toMap(p -> p.ID, p -> p));
            QMSPassRate wQMSPassRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassRate = new QMSPassRate();
                wQMSPassRate.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wQMSPassRate.OrderNo = StringUtils.parseString(wReader.get("OrderNo"));
                wQMSPassRate.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wQMSPassRate.RateDate = StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                wQMSPassRate.GoodFQTY = StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassRate.BadFQTY = StringUtils.parseInt(wReader.get("BadFQTY"));
                wQMSPassRate.BadComFQTY = StringUtils.parseInt(wReader.get("BadComFQTY"));
                wQMSPassRate.ReworkComFQTY = StringUtils.parseInt(wReader.get("ReworkComFQTY"));

                if (wFPCProductMap.containsKey(wQMSPassRate.ProductID)) {
                    wQMSPassRate.ProductName = wFPCProductMap.get(wQMSPassRate.ProductID).Name;
                    wQMSPassRate.ProductNo = wFPCProductMap.get(wQMSPassRate.ProductID).Code;
                    wQMSPassRate.ProductTypeID = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeID;
                    wQMSPassRate.ProductTypeName = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeName;
                    wQMSPassRate.ComNumber = wFPCProductMap.get(wQMSPassRate.ProductID).ComponentNumber;
                    wQMSPassRate.getBadComFQTY();
                }
                wResult.add(wQMSPassRate);
            }

        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetPassRateByList", ex);
        }
        return wResult;
    }

    /**
     * 产品趋势统计
     *
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStatType
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassRate> QMS_GetProductPassRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID,
                                                        String wOrderNo, int wStatType,
                                                        Calendar wStartDate, Calendar wEndDate,
                                                        OutResult<Integer> wErrorCode) {
        List<QMSPassRate> wResult = new ArrayList<>();
        try {
            StatTypes wType = StatTypes.getEnumType(wStatType);
            String wGroupType = "";
            switch (wType) {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case Month:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(t.OffLineTime),'-',(quarter(t.OffLineTime) * 3)-2,'-1')" +
                            ",'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(t.OffLineTime),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(t.OffLineTime,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }

            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText = StringUtils.Format("SELECT  t.ProductID,  {1}  as StatTypeDate," +
                    "  count(case when t.Status=:wFinishStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as GoodFQTY," +
                    "  count(case when t.ReworkFlag=1 and t.ComponentType= :wComType then t.ID else " +
                    "null end) as ReworkComFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as BadFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wComType then t.ID else null end)" +
                    " as BadComFQTY" +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t" +
                    ".OffLineTime) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.OffLineTime)  " +
                    "  GROUP BY t.ProductID, StatTypeDate", getTableName(), wGroupType);


            List<Map<String, Object>> wQueryResult = this.queryForList(wSQLText, wParams);

            if (wQueryResult == null || wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FPCProduct> wFPCProductMap =
                    CacheUtils.GetCacheTList(FPCProduct.class).stream().collect(Collectors.toMap(p -> p.ID, p -> p,
                            (o1, o2) -> o1.Active == 1 ? o1 : o2));
            QMSPassRate wQMSPassRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassRate = new QMSPassRate();
                wQMSPassRate.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wQMSPassRate.RateDate = StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                wQMSPassRate.GoodFQTY = StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassRate.BadFQTY = StringUtils.parseInt(wReader.get("BadFQTY"));
                wQMSPassRate.BadComFQTY = StringUtils.parseInt(wReader.get("BadComFQTY"));
                wQMSPassRate.ReworkComFQTY = StringUtils.parseInt(wReader.get("ReworkComFQTY"));

                if (wFPCProductMap.containsKey(wQMSPassRate.ProductID)) {
                    wQMSPassRate.ProductName = wFPCProductMap.get(wQMSPassRate.ProductID).Name;
                    wQMSPassRate.ProductNo = wFPCProductMap.get(wQMSPassRate.ProductID).Code;
                    wQMSPassRate.ProductTypeID = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeID;
                    wQMSPassRate.ProductTypeName = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeName;
                    wQMSPassRate.ComNumber = wFPCProductMap.get(wQMSPassRate.ProductID).ComponentNumber;
                    wQMSPassRate.getBadComFQTY();
                }
                wResult.add(wQMSPassRate);
            }
        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetProductPassRateByList", ex);
        }
        return wResult;
    }


    /**
     * 订单统计
     *
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassRate> QMS_GetPassRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID, String wOrderNo,
                                                 Calendar wStartDate, Calendar wEndDate,
                                                 OutResult<Integer> wErrorCode) {
        List<QMSPassRate> wResult = new ArrayList<>();
        try {


            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText = StringUtils.Format("SELECT t.OrderID,t.OrderNo,t.ProductID," +
                    "  count(case when t.Status=:wFinishStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as GoodFQTY," +
                    "  count(case when t.ReworkFlag=1 and t.ComponentType= :wComType then t.ID else " +
                    "null end) as ReworkComFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as BadFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wComType then t.ID else null end)" +
                    " as BadComFQTY" +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t" +
                    ".OffLineTime) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.OffLineTime)  " +
                    "  GROUP BY t.OrderID", getTableName());

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQLText, wParams);

            if (wQueryResult == null || wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FPCProduct> wFPCProductMap = CacheUtils.GetCacheTList(FPCProduct.class).stream()
                    .collect(Collectors.toMap(p -> p.ID, p -> p));
            QMSPassRate wQMSPassRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassRate = new QMSPassRate();
                wQMSPassRate.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wQMSPassRate.OrderNo = StringUtils.parseString(wReader.get("OrderNo"));
                wQMSPassRate.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wQMSPassRate.GoodFQTY = StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassRate.BadFQTY = StringUtils.parseInt(wReader.get("BadFQTY"));
                wQMSPassRate.BadComFQTY = StringUtils.parseInt(wReader.get("BadComFQTY"));
                wQMSPassRate.ReworkComFQTY = StringUtils.parseInt(wReader.get("ReworkComFQTY"));

                if (wFPCProductMap.containsKey(wQMSPassRate.ProductID)) {
                    wQMSPassRate.ProductName = wFPCProductMap.get(wQMSPassRate.ProductID).Name;
                    wQMSPassRate.ProductNo = wFPCProductMap.get(wQMSPassRate.ProductID).Code;
                    wQMSPassRate.ProductTypeID = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeID;
                    wQMSPassRate.ProductTypeName = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeName;
                    wQMSPassRate.ComNumber = wFPCProductMap.get(wQMSPassRate.ProductID).ComponentNumber;
                    wQMSPassRate.getBadComFQTY();
                }
                wResult.add(wQMSPassRate);
            }

        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetPassRateByList", ex);
        }
        return wResult;
    }

    /**
     * 产品统计
     *
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassRate> QMS_GetProductPassRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID,
                                                        String wOrderNo,
                                                        Calendar wStartDate, Calendar wEndDate,
                                                        OutResult<Integer> wErrorCode) {
        List<QMSPassRate> wResult = new ArrayList<>();
        try {


            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText = StringUtils.Format("SELECT  t.ProductID, " +
                    "  count(case when t.Status=:wFinishStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as GoodFQTY," +
                    "  count(case when t.ReworkFlag=1 and t.ComponentType= :wComType then t.ID else " +
                    "null end) as ReworkComFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wProductComType then t.ID else " +
                    "null end) as BadFQTY," +
                    "  count(case when t.Status=:wScrapStatus and t.ComponentType= :wComType then t.ID else null end)" +
                    " as BadComFQTY" +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t" +
                    ".OffLineTime) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.OffLineTime)  " +
                    "  GROUP BY t.ProductID", getTableName());


            List<Map<String, Object>> wQueryResult = this.queryForList(wSQLText, wParams);

            if (wQueryResult == null || wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FPCProduct> wFPCProductMap =
                    CacheUtils.GetCacheTList(FPCProduct.class).stream().collect(Collectors.toMap(p -> p.ID, p -> p,
                            (o1, o2) -> o1.Active == 1 ? o1 : o2));
            QMSPassRate wQMSPassRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassRate = new QMSPassRate();
                wQMSPassRate.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wQMSPassRate.GoodFQTY = StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassRate.BadFQTY = StringUtils.parseInt(wReader.get("BadFQTY"));
                wQMSPassRate.BadComFQTY = StringUtils.parseInt(wReader.get("BadComFQTY"));
                wQMSPassRate.ReworkComFQTY = StringUtils.parseInt(wReader.get("ReworkComFQTY"));

                if (wFPCProductMap.containsKey(wQMSPassRate.ProductID)) {
                    wQMSPassRate.ProductName = wFPCProductMap.get(wQMSPassRate.ProductID).Name;
                    wQMSPassRate.ProductNo = wFPCProductMap.get(wQMSPassRate.ProductID).Code;
                    wQMSPassRate.ProductTypeID = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeID;
                    wQMSPassRate.ProductTypeName = wFPCProductMap.get(wQMSPassRate.ProductID).ProductTypeName;
                    wQMSPassRate.ComNumber = wFPCProductMap.get(wQMSPassRate.ProductID).ComponentNumber;
                    wQMSPassRate.getBadComFQTY();
                }
                wResult.add(wQMSPassRate);
            }
        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetProductPassRateByList", ex);
        }
        return wResult;
    }

    /**
     * 通过物料条码和时间段查询工件记录ID集合
     */
    public List<Integer> SFC_QueryListByBatchNumber(BMSEmployee wLoginUser, String wBatchNumber, Calendar wStartTime,
                                                    Calendar wEndTime, Pagination wPagination,
                                                    OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2010, 0, 1, 0, 0, 0);
            if (wStartTime.compareTo(wBaseTime) <= 0 || wEndTime.compareTo(wBaseTime) <= 0 || StringUtils.isEmpty(wBatchNumber)) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t2.`ID` FROM {0}.`sfc_task_ipt` t " +
                            "INNER JOIN {0}.`sfc_material_loading_record` t1 ON t.ID=t1.TaskIPTID " +
                            "INNER JOIN {0}.`sfc_workpiece_record` t2 ON t.`ComponentCode`=t2.`Code` " +
                            "WHERE t1.`BatchNumber`=:BatchNumber AND t.`CreateTime`>=:StartTime AND t" +
                            ".`CreateTime`<=:EndTime;",
                    DBSchemas.MES.getDBName());

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("BatchNumber", wBatchNumber);
            wParamMap.put("StartTime", wStartTime);
            wParamMap.put("EndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap, wPagination);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            log.error("SFC_QueryListByBatchNumber", ex);
        }
        return wResult;
    }

    /**
     * 获取最新的工件已检验的订单集合
     */
    public List<Integer> SFC_GetNewOrderIDList(BMSEmployee wLoginUser, int wLimitNum, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            String wSQL = StringUtils.Format("SELECT DISTINCT OrderID FROM {0}.sfc_workpiece_record WHERE " +
                            "CheckResult>0 " +
                            "ORDER BY ID DESC LIMIT :wLimitNum;",
                    DBSchemas.MES.getDBName());

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wLimitNum", wLimitNum);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("OrderID")));
            }
        } catch (Exception ex) {
            log.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 统计订单合格率
     */
    public List<AndonOrderRate> SFC_QueryOrderRateList(BMSEmployee wLoginUser, Map<Integer, String> wIDCodeOrderMap,
                                                       List<Integer> wOrderIDList,
                                                       OutResult<Integer> wErrorCode) {
        List<AndonOrderRate> wResult = new ArrayList<>();
        try {
            if (wOrderIDList == null || wOrderIDList.size() == 0) {
                return wResult;
            }
            String wSQL = StringUtils.Format("SELECT    " +
                            "    OrderID,   " +
                            "    SUM(CASE WHEN CheckResult = 1 THEN 1 ELSE 0 END) AS GoodCount,   " +
                            "    SUM(CASE WHEN CheckResult = 2 THEN 1 ELSE 0 END) AS BadCount   " +
                            "FROM    " +
                            "    {0}.`sfc_workpiece_record`   " +
                            "WHERE CheckResult>0 AND OrderID IN ({1}) " +
                            "GROUP BY    " +
                            "    OrderID;",
                    DBSchemas.MES.getDBName(), StringUtils.Join(",", wOrderIDList));

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wOrderID = StringUtils.parseInt(wReader.get("OrderID"));
                int wGoodCount = StringUtils.parseInt(wReader.get("GoodCount"));
                int wBadCount = StringUtils.parseInt(wReader.get("BadCount"));

                float qualityRate = 0;
                if (wGoodCount + wBadCount > 0) {
                    qualityRate = BigDecimal.valueOf((double) wGoodCount / (wGoodCount + wBadCount))
                            .setScale(3, RoundingMode.DOWN).floatValue();
                }
                AndonOrderRate wAndonOrderRate = new AndonOrderRate(wIDCodeOrderMap.get(wOrderID), wGoodCount,
                        wBadCount, qualityRate);
                wResult.add(wAndonOrderRate);
            }
        } catch (Exception ex) {
            log.error("SFC_QueryOrderRateList", ex);
        }
        return wResult;
    }

    public List<Integer> SFC_GetNewProductIDList(BMSEmployee wLoginUser, int wLimitNum, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            String wSQL = StringUtils.Format("SELECT DISTINCT ProductID FROM {0}.sfc_workpiece_record WHERE " +
                            "CheckResult>0 " +
                            "ORDER BY ID DESC LIMIT :wLimitNum;",
                    DBSchemas.MES.getDBName());

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wLimitNum", wLimitNum);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ProductID")));
            }
        } catch (Exception ex) {
            log.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取产品合格率数据
     */
    public List<AndonProductRate> SFC_QueryProductRateList(BMSEmployee wLoginUser,
                                                           Map<Integer, String> wIDCodeProductMap,
                                                           List<Integer> wProductIDList,
                                                           OutResult<Integer> wErrorCode) {
        List<AndonProductRate> wResult = new ArrayList<>();
        try {
            if (wProductIDList == null || wProductIDList.size() == 0) {
                return wResult;
            }
            String wSQL = StringUtils.Format("SELECT    " +
                            "    ProductID,   " +
                            "    SUM(CASE WHEN CheckResult = 1 THEN 1 ELSE 0 END) AS GoodCount,   " +
                            "    SUM(CASE WHEN CheckResult = 2 THEN 1 ELSE 0 END) AS BadCount   " +
                            "FROM    " +
                            "    {0}.`sfc_workpiece_record`   " +
                            "WHERE CheckResult>0 AND ProductID IN ({1}) " +
                            "GROUP BY    " +
                            "    ProductID;",
                    DBSchemas.MES.getDBName(), StringUtils.Join(",", wProductIDList));

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wProductID = StringUtils.parseInt(wReader.get("ProductID"));
                int wGoodCount = StringUtils.parseInt(wReader.get("GoodCount"));
                int wBadCount = StringUtils.parseInt(wReader.get("BadCount"));

                float qualityRate = 0;
                if (wGoodCount + wBadCount > 0) {
                    qualityRate = BigDecimal.valueOf((double) wGoodCount / (wGoodCount + wBadCount))
                            .setScale(3, RoundingMode.DOWN).floatValue();
                }
                AndonProductRate wAndonProductRate = new AndonProductRate(wIDCodeProductMap.get(wProductID), wGoodCount,
                        wBadCount, qualityRate);
                wResult.add(wAndonProductRate);
            }
        } catch (Exception ex) {
            log.error("SFC_QueryOrderRateList", ex);
        }
        return wResult;
    }

    public List<Integer> SFC_GetRecordIDList(BMSEmployee wLoginUser, String wComponentCodes,
                                             OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            if (StringUtils.isEmpty(wComponentCodes)) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.sfc_workpiece_record where Code in (''{1}'');",
                    DBSchemas.MES.getDBName(), wComponentCodes);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            log.error("SFC_GetRecordIDList", ex);
        }
        return wResult;
    }

    public List<Integer> SFC_GetRelaRecordIDList(BMSEmployee wLoginUser, String wComponentCodes,
                                             OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            if (StringUtils.isEmpty(wComponentCodes)) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.sfc_workpiece_record where  " +
                            " PackageNo in (''{1}'')  or CaseNo in (''{1}'')  or BoxNo in (''{1}'') ;",
                    DBSchemas.MES.getDBName(), wComponentCodes);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(StringUtils.parseInt(wReader.get("ID")));
            }
        } catch (Exception ex) {
            log.error("SFC_GetRecordIDList", ex);
        }
        return wResult;
    }
}
